<?php
namespace app\admin\controller;
use cmf\controller\AdminBaseController;
use think\Db;
use think\Loader;
use think\Request;
use think\paginator\driver\Bootstrap;

ini_set('memory_limit','2048M');
class OmgController extends AdminBaseController
{

    public function _initialize()
    {
        parent::_initialize();
    }

    //omg_apply添加
    public function applyExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excel_array = $this->excelRead($excel_url);

                if ($excel_array[0][0] != '部门' && $excel_array[0][1] != '部门负责人') {
                    $this->error('确认表格是否正确');
                }


                array_shift($excel_array);  //删除第一个数组(标题);
		

                // //每行进行补全，空白行用上行数据填充
                // foreach ($excel_array as $key => $value) {
                //     foreach ($value as $k => $v) {
                //         if ($key != 0 && $v == null) {
                //             $excel_array[$key][$k] = $excel_array[$key-1][$k];
                //         }
                //     }
                // }
                // unset($key,$value,$k,$v);

                //去掉中间空行，以部门、组长、主体最为根据
                foreach ($excel_array as $key => $value) {
                    if (strlen($value[0]) <=  0 && strlen($value[1]) <=  0 && strlen($value[3]) <=  0  && strlen($value[4]) <=  0 || is_numeric($value[1])) {
                        continue;
                    }else{
                        $apply_array[] = $value;
                    }
                }
                unset($key,$value,$excel_array);
               
                foreach ($apply_array as $key => $value) {

                    if ($value[11] === '') {
                        $data[] = array(
                            'department'        => $value[0],
                            'master'            => $value[1],
                            'second_master'     => $value[2],
                            'main'              => $value[3],
                            'domain'            => $value[4],
                            'website_category'  => $value[5],
                            'agent'             => $value[6],
                            'a_num'             => $value[7],
                            'a_time'            => $value[9],
                            );
                    }
                    
                }           

                if (isset($data)) {
                    Db::name('omg_apply')->insertAll($data);
                    $this->success('导入成功' , url("omg/applylist"));
                }else{
                    $this->error('没有数据导入');
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

	//申户记录列表
	public function applyList()
	{
		$content = hook_one('admin_omg_applylist_view');
		if (!empty($content)) {
			return $content;
		}

        $selects = trim(input('post.name'));

		$where = [];
		// $whereor = [];
        if($selects){            
            $where['department|master|main|domain|agent'] = ['like','%'.$selects.'%'];
        }

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  date("Y-m-d",strtotime(input('post.star_time')));
            $end_time =  date("Y-m-d",strtotime(input('post.end_time')));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['a_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
        }

		$apply_lists = Db::name('omg_apply')
						->where($where)
						// ->whereOr($whereor)
                        ->where($where_time)
						->order(['id DESC'])
						->paginate(30, false, [
                            'query' => request()->param(),
                        ]);
			
        $page = $apply_lists->render();
        $this->assign("page", $page);
        $this->assign('apply_lists',$apply_lists);
		return $this->fetch();
	}

    //申户记录修改
    public function applyEdit()
    {
        $id = input("param.id");
        $one_apply = Db::name('omg_apply')->where('id',$id)->find();
        $this->assign('one_apply',$one_apply);
        return $this->fetch('apply_edit');
    }

    //保存修改
    public function applyEditSave()
    {
        if ($this->request->isPost()) {
            $data = input("post.");
            $data['u_time'] = date("Y-m-d H:i:s");

            $re = Db::name('omg_apply')->update($data);
            if ($re) {
                $this->success('修改成功' , url('omg/applyList'));
            }else{
                $this->error('修改失败');
            }
        }
    }

    //omg_down添加
    public function downExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excel_array = $this->excelRead($excel_url);

                if ($excel_array[0][0] != '部门' && $excel_array[0][1] != '部门负责人') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excel_array);  //删除第一个数组(标题);
                // array_shift($excel_array);  //删除第一个数组(标题);
                // array_shift($excel_array);  //删除第一个数组(标题);

                // //每行进行补全，空白行用上行数据填充
                // foreach ($excel_array as $key => $value) {
                //     foreach ($value as $k => $v) {
                //         if ($key != 0 && $v == null) {
                //             $excel_array[$key][$k] = $excel_array[$key-1][$k];
                //         }
                //     }
                // }
                // unset($key,$value,$k,$v);

                //去掉中间空行，以部门、账号、公司名称做为根据
                foreach ($excel_array as $key => $value) {
                    if (strlen($value[0]) <=  0 && strlen($value[3]) <=  0 && strlen($value[4]) <=  0 || is_numeric($value[0])) {
                        continue;
                    }else{
                        $down_array[] = $value;
                    }
                }
                unset($key,$value,$excel_array);

                foreach ($down_array as $key => $value) {
                    if ($value[14] === '') {
                        $data[] = array(
                            'department'        => $value[0],
                            'master'            => $value[1],
                            'second_master'     => $value[2],
                            'account'           => $value[3],
                            'domain'            => $value[4],
                            'main'              => $value[5],
                            'agent'             => $value[6],
                            'd_time'            => $value[8],   
                            'is_ok'             => $value[9],   //是否可用
                            'no_reason'         => $value[10],    //不可用原因
                            'is_quota'          => $value[11],    //是否限额
                            'comment'           => $value[13],
                            );
                    }
                    
                }           
                
                if (isset($data)) {
                    Db::name('omg_down')->insertAll($data);
                    $this->success('导入成功' , url("omg/downlist"));
                }else{
                    $this->error('没有数据导入');
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

    //下户记录列表
    public function downList()
    {
        $content = hook_one('admin_omg_downlist_view');
        if (!empty($content)) {
            return $content;
        }

        $selects = trim(input('post.name'));

        $where = [];
        // $whereor = [];
        if($selects){            
            $where['department|account|domain|main|agent'] = ['like','%'.$selects.'%'];
        }

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  date("Y-m-d",strtotime(input('post.star_time')));
            $end_time =  date("Y-m-d",strtotime(input('post.end_time')));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['d_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
        }

        $down_lists = Db::name('omg_down')
                        ->where($where)
                        // ->whereOr($whereor)
                        ->where($where_time)
                        ->order(['id DESC'])
                        ->paginate(30, false, [
                            'query' => request()->param(),
                        ]);
            
        $page = $down_lists->render();
        $this->assign("page", $page);
        $this->assign('down_lists',$down_lists);
        return $this->fetch();
    }

    //下户记录修改
    public function downEdit()
    {
        $id = input("param.id");
        $one_down = Db::name('omg_down')->where('id',$id)->find();

        $this->assign('one_down',$one_down);
        return $this->fetch();
    }

    //保存修改
    public function downEditSave()
    {
        if ($this->request->isPost()) {
            $data = input("post.");
            $data['u_time'] = date("Y-m-d H:i:s");

            $re = Db::name('omg_down')->update($data);
            if ($re) {
                $this->success('修改成功' , url('omg/downList'));
            }else{
                $this->error('修改失败');
            }
        }
    }

    //户状态导入
    public function accStatusExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excel_array = $this->excelRead($excel_url);

                if ($excel_array[0][2] != 'BM ID' && $excel_array[0][3] != '广告账户') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excel_array);  //删除第一个数组(标题);

                // //每行进行补全，空白行用上行数据填充
                // foreach ($excel_array as $key => $value) {
                //     foreach ($value as $k => $v) {
                //         if ($key != 0 && $v == null) {
                //             $excel_array[$key][$k] = $excel_array[$key-1][$k];
                //         }
                //     }
                // }
                // unset($key,$value,$k,$v);

                //去掉中间空行，以部门、账号、公司名称做为根据
                foreach ($excel_array as $key => $value) {
                    if (strlen($value[2]) <=  0 && strlen($value[3]) <=  0 ) {
                        continue;
                    }else{
                        $acc_status_array[] = $value;
                    }
                }
                unset($key,$value,$excel_array);
               
                foreach ($acc_status_array as $key => $value) {
                    $data = array(
                        'bm_id' => $value[2],
                        'is_ok' => $value[6],
                        'no_reason' => $value[7],
                        'is_quota' => $value[8],
                        'acc_source' => $value[9],
                        'comment' => $value[10],
                        );

                    $up_re = Db::name('omg_down')->where('account' , $value[3])->update($data);
                    if (!$up_re) {
                        $error = isset($error) ? $error.$value[3]."<br/>" : $value[3]."<br/>";
                    }
                }           
                
                if ($error) {
                    echo $error.' 以上数据导入失败，其他正常数据导入完成';die;
                }else{
                    $this->success('数据导入成功' , url("omg/downlist"));
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

    //代理分组统计数量
    public function agentCount()
    {
        $content = hook_one('admin_omg_agentcount_view');
        if (!empty($content)) {
            return $content;
        }

        //周统计、月统计
        if (input("param.oncheck") == 'week') {
            $star_time =  date("Y-m-d",mktime(0, 0 , 0,date("m"),date("d")-date("w")+1,date("Y")));
            $end_time =  date("Y-m-d",mktime(23,59,59,date("m"),date("d")-date("w")+7,date("Y")));

            $where_time['a_time'] = array('between',array($star_time,$end_time));
            $where_dtime['d_time'] = array('between',array($star_time,$end_time));

            $active = 'week';
        }elseif (input("param.oncheck") == 'month') {
            $star_time =  date("Y-m-d",mktime(0, 0 , 0,date("m"),1,date("Y")));
            $end_time =  date("Y-m-d",mktime(23,59,59,date("m"),date("t"),date("Y")));

            $where_time['a_time'] = array('between',array($star_time,$end_time));
            $where_dtime['d_time'] = array('between',array($star_time,$end_time));

            $active = 'month';
        }else{
            $active = 'count';

            //查询时间段
            if (input('post.star_time') != '') {
                $star_time =  date("Y-m-d",strtotime(input('post.star_time')));
                $end_time =  date("Y-m-d",strtotime(input('post.end_time')));
                // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
                $where_time['a_time'] = array('between',array($star_time,$end_time));
                $where_dtime['d_time'] = array('between',array($star_time,$end_time));

            }else{
                $where_time = '';
                $where_dtime = '';
            }
        }

        

        $allAgent = Db::name('nge_agent')->order('id DESC')->select()->toArray();
        $total_num = array();

        foreach ($allAgent as $key => $value) {
            //申户数
            $apply_num = Db::name('omg_apply')->where(['agent'=>$value['name']])->where($where_time)->sum('a_num');
		//echo Db::name('omg_apply')->getLastSql();die;

            if ($apply_num > 0) {
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = $apply_num;

                //下户数
                $down_num = Db::name('omg_down')->where(['agent'=>$value['name']])->where($where_dtime)->count('id');
                $all_agent[$key]['total_down_num'] = $down_num;
		//echo Db::name('omg_down')->getLastSql();die;
                //提交站数
                $apply_domain_num = Db::name('omg_apply')->where(['agent'=>$value['name']])->where($where_time)->group('main,agent')->count('id');
                $all_agent[$key]['total_apply_domain_num'] = $apply_domain_num;

                //下户站数
                $down_domain_num = Db::name('omg_down')->where(['agent'=>$value['name']])->where($where_dtime)->group('main,agent')->count('id');
                $all_agent[$key]['total_down_domain_num'] = $down_domain_num;
            }else{
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = 0;
                $all_agent[$key]['total_down_num'] = 0;
                $all_agent[$key]['total_apply_domain_num'] = 0;
                $all_agent[$key]['total_down_domain_num'] = 0;

                $down_num = 0;
                $apply_num = 0;
                $apply_domain_num = 0;
                $down_domain_num = 0;
            }

            $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] + $apply_num : $apply_num;
            $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] + $down_num : $down_num;
            $total_num['total_apply_domain_num'] = isset($total_num['total_apply_domain_num']) ? $total_num['total_apply_domain_num'] + $apply_domain_num : $apply_domain_num;
            $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] + $down_domain_num : $down_domain_num;

        }

        $total_num['name'] = '总计';
        //空数据
        $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] : 0;
        $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] : 0;
        $total_num['total_apply_domain_num'] = isset($total_num['total_apply_domain_num']) ? $total_num['total_apply_domain_num'] : 0;
        $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] : 0;

        $all_agent = array_merge($all_agent);

        //计算各代理占比
        foreach ($all_agent as $ka => $va) {
            if ($total_num['total_down_num'] > 0) {
                $all_agent[$ka]['down_proportion'] = round($va['total_down_num'] / $total_num['total_down_num'] * 100).'%';
            }else{
                $all_agent[$ka]['down_proportion'] = 0;
            }

            if ($total_num['total_down_domain_num'] > 0) {
                $all_agent[$ka]['down_domain_proportion'] = round($va['total_down_domain_num'] / $total_num['total_down_domain_num'] * 100).'%';
            }else{
                $all_agent[$ka]['down_domain_proportion'] = 0;
            }
            
        }

        $this->assign('active',$active);
        $this->assign('total_num',$total_num);
        $this->assign('allAgent',$allAgent);
        $this->assign('all_agent',$all_agent);
        return $this->fetch();
    }

    //代理分组统计数量导出
    public function agentCountOut()
    {   
        Loader::import("PHPExcel.PHPExcel");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
        Loader::import("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel    = new \PHPExcel();
        $objWriter      = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter      = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  date("Y-m-d",strtotime(input('post.star_time')));
            $end_time =  date("Y-m-d",strtotime(input('post.end_time')));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['a_time'] = array('between',array($star_time,$end_time));
            $where_dtime['d_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
            $where_dtime = '';
        }

        $allAgent = Db::name('nge_agent')->order('id DESC')->select()->toArray();
        foreach ($allAgent as $key => $value) {
            //申户数
            $apply_num = Db::name('omg_apply')->where(['agent'=>$value['name']])->where($where_time)->sum('a_num');

            if ($apply_num > 0) {
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = $apply_num;

                //下户数
                $down_num = Db::name('omg_down')->where(['agent'=>$value['name']])->count('id');
                $all_agent[$key]['total_down_num'] = $down_num;

                //提交站数
                $apply_domain_num = Db::name('omg_apply')->where(['agent'=>$value['name']])->where($where_time)->group('main,agent')->count('id');
                $all_agent[$key]['total_apply_domain_num'] = $apply_domain_num;

                //下户站数
                $down_domain_num = Db::name('omg_down')->where(['agent'=>$value['name']])->where($where_dtime)->group('main,agent')->count('id');
                $all_agent[$key]['total_down_domain_num'] = $down_domain_num;
            }else{
                $all_agent[$key]['name'] = $value['name'];
                $all_agent[$key]['total_apply_num'] = 0;
                $all_agent[$key]['total_down_num'] = 0;
                $all_agent[$key]['total_apply_domain_num'] = 0;
                $all_agent[$key]['total_down_domain_num'] = 0;
            }

        }

        $all_agent = array_merge($all_agent);

        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '代理')
        ->setCellValue('B1', '提交站数')
        ->setCellValue('C1', '申户数')
        ->setCellValue('D1', '下户站数')
        ->setCellValue('E1', '下户数');

        //$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($all_agent); //计算有多少条数据

        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $all_agent[$i-2]['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $all_agent[$i-2]['total_apply_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $all_agent[$i-2]['total_apply_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $all_agent[$i-2]['total_down_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $all_agent[$i-2]['total_down_num']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->settitle(date("Y-m-d")); //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="代理申户下户数量统计表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }

    //z组统计数量
    public function groupCount()
    {
        $content = hook_one('admin_omg_groupcount_view');
        if (!empty($content)) {
            return $content;
        }

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  date("Y-m-d",strtotime(input('post.star_time')));
            $end_time =  date("Y-m-d",strtotime(input('post.end_time')));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['a_time'] = array('between',array($star_time,$end_time));
            $where_dtime['d_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
            $where_dtime = '';
        }

        $all_group = Db::name('omg_apply')->field('department')->group('department')->select()->toArray();

        $total_num = array();
        $total_num['success_rate'] = 0; 
        foreach ($all_group as $key => $value) {
            //申户数
            $all_group[$key]['department'] = $value['department'];
            $apply_num = Db::name('omg_apply')->where(['department'=>$value['department']])->where($where_time)->sum('a_num');

            //站长
            $master = Db::name('omg_apply')->field('master')->where(['department'=>$value['department']])->find();
            $all_group[$key]['master'] = isset($master) ? $master['master'] : '';

            if ($apply_num > 0) {
                $all_group[$key]['total_apply_num'] = $apply_num;

                //下户数
                $down_num = Db::name('omg_down')->where(['department'=>$value['department']])->count('id');

                $all_group[$key]['total_down_num'] = $down_num;

                //提交站数
                $apply_domain_num = Db::name('omg_apply')->where(['department'=>$value['department']])->where($where_time)->group('main,agent')->count('id');
                $all_group[$key]['total_apply_domain_num'] = $apply_domain_num;

                //下户站数
                $down_domain_num = Db::name('omg_down')->where(['department'=>$value['department']])->where($where_dtime)->group('main,agent')->count('id');
                $all_group[$key]['total_down_domain_num'] = $down_domain_num;

            }else{
                $all_group[$key]['department'] = $value['department'];
                $all_group[$key]['total_apply_num'] = 0;
                $all_group[$key]['total_down_num'] = 0;
                $all_group[$key]['total_apply_domain_num'] = 0;
                $all_group[$key]['total_down_domain_num'] = 0;
                $down_num = 0;
                $apply_num = 0;
                $apply_domain_num = 0;
                $down_domain_num = 0;
            }

            $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] + $apply_num : $apply_num;
            $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] + $down_num : $down_num;
            $total_num['total_apply_domain_num'] = isset($total_num['total_apply_domain_num']) ? $total_num['total_apply_domain_num'] + $apply_domain_num : $apply_domain_num;
            $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] + $down_domain_num : $down_domain_num;
        }

        //计算成功率
        foreach ($all_group as $allk => $allv) {
            $all_group[$allk]['group'] = $allk;
            if ($allv['total_apply_domain_num'] == 0) {
                $all_group[$allk]['success_rate'] = '0';
            }else{
                $all_group[$allk]['success_rate'] = round($allv['total_down_domain_num'] / $allv['total_apply_domain_num'],2) * 100 .'%';
            }   
        }
         
        $total_num['master'] = '';
        $total_num['department'] = '总计';
        //空数据
        $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] : '';
        $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] : '';
        $total_num['total_apply_domain_num'] = isset($total_num['total_apply_domain_num']) ? $total_num['total_apply_domain_num'] : '';
        $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] : '';

        if ($total_num['total_apply_domain_num'] == 0) {
            $total_num['success_rate'] = 0;
        }else{
           $total_num['success_rate'] = isset($total_num['success_rate'])  ? round($total_num['total_down_domain_num'] / $total_num['total_apply_domain_num'],2) * 100 .'%' : ''; 
        }
        $all_group['总计'] = $total_num;

        foreach ($all_group as $k => $v) {
            $outAll[] = $v;
        }

        $this->assign('outAll',$outAll);
        return $this->fetch();
    }

    //z组统计数量导出
    public function groupCountOut()
    {
        Loader::import("PHPExcel.PHPExcel");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
        Loader::import("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel    = new \PHPExcel();
        $objWriter      = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter      = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        //查询时间段
        if (input('post.star_time') != '') {
            $star_time =  date("Y-m-d",strtotime(input('post.star_time')));
            $end_time =  date("Y-m-d",strtotime(input('post.end_time')));
            // $where_time = " AND apply_time BETWEEN '$star_time' AND '$end_time' ";
            $where_time['a_time'] = array('between',array($star_time,$end_time));

        }else{
            $where_time = '';
        }

        $all_group = Db::name('omg_apply')->field('department')->group('department')->select()->toArray();

        foreach ($all_group as $key => $value) {
            //申户数
            $all_group[$key]['department'] = $value['department'];
            $apply_num = Db::name('omg_apply')->where(['department'=>$value['department']])->where($where_time)->sum('a_num');

            //站长
            $master = Db::name('omg_apply')->field('master')->where(['department'=>$value['department']])->find();
            $all_group[$key]['master'] = isset($master) ? $master['master'] : '';

            if ($apply_num > 0) {
                $all_group[$key]['total_apply_num'] = $apply_num;

                //下户数
                $down_num = Db::name('omg_down')->where(['department'=>$value['department']])->count('id');
                $all_group[$key]['total_down_num'] = $down_num;

                //提交站数
                $apply_domain_num = Db::name('omg_apply')->where(['department'=>$value['department']])->where($where_time)->group('main,agent')->count('id');
                $all_group[$key]['total_apply_domain_num'] = $apply_domain_num;

                //下户站数
                $down_domain_num = Db::name('omg_down')->where(['department'=>$value['department']])->where($where_time)->group('main,agent')->count('id');
                $all_group[$key]['total_down_domain_num'] = $down_domain_num;

            }else{
                $all_group[$key]['department'] = $value['department'];
                $all_group[$key]['total_apply_num'] = 0;
                $all_group[$key]['total_down_num'] = 0;
                $all_group[$key]['total_apply_domain_num'] = 0;
                $all_group[$key]['total_down_domain_num'] = 0;
            }

            $total_num['total_apply_num'] = isset($total_num['total_apply_num']) ? $total_num['total_apply_num'] + $apply_num : $apply_num;
            $total_num['total_down_num'] = isset($total_num['total_down_num']) ? $total_num['total_down_num'] + $down_num : $down_num;
            $total_num['total_apply_domain_num'] = isset($total_num['total_apply_domain_num']) ? $total_num['total_apply_domain_num'] + $apply_domain_num : $apply_domain_num;
            $total_num['total_down_domain_num'] = isset($total_num['total_down_domain_num']) ? $total_num['total_down_domain_num'] + $down_domain_num : $down_domain_num;
        }

        //计算成功率
        foreach ($all_group as $allk => $allv) {
            $all_group[$allk]['group'] = $allk;
            if ($allv['total_apply_domain_num'] == 0) {
                $all_group[$allk]['success_rate'] = '0';
            }else{
                $all_group[$allk]['success_rate'] = round($allv['total_down_domain_num'] / $allv['total_apply_domain_num'],2) * 100 .'%';
            }   
        }

        $total_num['success_rate'] = round($total_num['total_down_domain_num'] / $total_num['total_apply_domain_num'],2) * 100 .'%'; 
        $total_num['master'] = '';
        $total_num['department'] = '总计';

        $all_group['总计'] = $total_num;

        foreach ($all_group as $k => $v) {
            $outAll[] = $v;
        }

        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '部门')
        ->setCellValue('B1', '站长')
        ->setCellValue('C1', '提交站数')
        ->setCellValue('D1', '申户数')
        ->setCellValue('E1', '下户站数')
        ->setCellValue('F1', '下户数')
        ->setCellValue('G1', '成功率');
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        //$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($outAll); //计算有多少条数据
        // echo $count;
        // die;
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $outAll[$i-2]['department']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $outAll[$i-2]['master']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $outAll[$i-2]['total_apply_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $outAll[$i-2]['total_apply_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $outAll[$i-2]['total_down_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $outAll[$i-2]['total_down_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $outAll[$i-2]['success_rate']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->settitle(date("Y-m-d")); //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="站长申户下户数量统计表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }

    public function excelOut()
    {
        Loader::import("PHPExcel.PHPExcel");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
        Loader::import("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel    = new \PHPExcel();
        $objWriter      = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter      = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '部门')
        ->setCellValue('B1', '站长')
        ->setCellValue('C1', '提交站数')
        ->setCellValue('D1', '申户数')
        ->setCellValue('E1', '下户站数')
        ->setCellValue('F1', '下户数')
        ->setCellValue('G1', '成功率');
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        //$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($outAll); //计算有多少条数据
        // echo $count;
        // die;
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $outAll[$i-2]['group']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $outAll[$i-2]['stationmaster']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $outAll[$i-2]['total_apply_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $outAll[$i-2]['total_apply_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $outAll[$i-2]['total_down_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $outAll[$i-2]['total_down_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $outAll[$i-2]['success_rate']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->settitle(date("Y-m-d")); //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="站长申户下户数量统计表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件

    }

    /**
     *读取excel数据
     *string $excel_url  excel路径
     *return array $excel_array 读取数组
     */
    public function excelRead($excel_url)
    {
        vendor('PHPExcel.Classes.PHPExcel');
        vendor('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        vendor('PHPExcel.Classes.PHPExcel.Reader.Excel5');

        $filename = ROOT_PATH.'public/upload/'.$excel_url;  //文件路径
        $extension = cmf_get_file_extension($excel_url);    //文件扩展名
        if($extension == 'xlsx'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else{
            $this->error('请上传Excel格式的文件！','');
        }
		
        $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式

        $excelArray = trimExcelStrBlank($excel_array);
        return TrimArray($excelArray);
    }

	public function index()
	{
		$content = hook_one('admin_domainname_index_view');
		if (!empty($content)) {
			return $content;
		}
		return $this->fetch();
	}
}
